package vivek.project.pocketmoney.common
{
	import flash.data.*;
	import flash.errors.*;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	import flash.net.FileReference;
	import flash.system.System;
	
	import mx.controls.*;

	public class CreateDatabaseSQLScript
	{

		public function CreateDatabaseSQLScript():void
		{
			if (dataFile.exists)
			{
				var destination:File=File.documentsDirectory;
				destination=destination.resolvePath("pm_dat.bak");
				try
				{
					dataFile.moveTo(destination, true);
				}
				catch (error:Error)
				{
					Alert(error.message);
				}
			}
			createDatabaseSchema();
			loadSeedData();
		}
		private var _sqlConnection:SQLConnection;
		private var dataFile:File=File.applicationStorageDirectory.resolvePath("pm.dat");

		private function createDatabaseSchema():void
		{
			var sqlFile:File=File.applicationDirectory.resolvePath("vivek/project/pocketmoney/common/DatabaseSchema.sql");
			if (sqlFile.exists)
			{
				_sqlConnection=new SQLConnection();
				var _statement:SQLStatement=new SQLStatement();
				_sqlConnection.open(dataFile, SQLMode.CREATE);
				var fileStream:FileStream=new FileStream();
				fileStream.open(sqlFile, FileMode.READ);
				var fileInfo:String=fileStream.readUTFBytes(fileStream.bytesAvailable);
				fileStream.close();
				var parts:Array=fileInfo.split('\n');
				_statement.sqlConnection=_sqlConnection;
				for (var i:int; i < parts.length; i++)
				{
					try
					{
						if ('' != parts[i])
						{
							_statement.text=parts[i];
							_statement.execute();
						}
					}
					catch (error:SQLError)
					{
						_sqlConnection.close();
						Alert.show(error.details);
						//System.exit(0);
						return;
					}
				}
				_sqlConnection.close();
			}
		}

		private function loadSeedData():void
		{
			var sqlFile:File=File.applicationDirectory.resolvePath("vivek/project/pocketmoney/common/SeedData.sql");
			if (sqlFile.exists)
			{
				var datafile:File=File.applicationStorageDirectory.resolvePath("pm.dat");
				_sqlConnection.open(dataFile, SQLMode.UPDATE);
				var fileStream:FileStream=new FileStream();
				fileStream.open(sqlFile, FileMode.READ);
				var fileInfo:String=fileStream.readUTFBytes(fileStream.bytesAvailable);
				fileStream.close();
				var parts:Array=fileInfo.split('\n');
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				for (var i:int; i < parts.length; i++)
				{
					try
					{
						if ('' != parts[i])
						{
							_statement.text=parts[i];
							_statement.execute();
						}
					}
					catch (error:SQLError)
					{
						_sqlConnection.rollback();
						trace(i + " : " + error.details);
						Alert.show(error.details);
					}
				}
				if (_sqlConnection.inTransaction)
					_sqlConnection.commit();
				_sqlConnection.close();
				_sqlConnection=null;
				_statement=null;
				parts=[];
			}
		}
	}
}